<?php

namespace PolarisbackEndFrameRe\api\database\engine;

/**
 * 创建并实例化SQLITE3服务器
 * @author TINUOKAS1/Astoflow
 */
require 'vendor/autoload.php';

use PolarisbackEndFrameRe\defined\Contents;
use SQLite3;

class CreateAndInitialize implements Contents
{

    protected $db;

    /**
     * 创建并实例化SQLITE3
     * Constructor. No Ret
     */
    public function __construct()
    {
        if (file_exists(self::SQLITE_3)) {
            $this->connect();
            $this->createTablesIfNotExists();
        } else {
            echo "未找到SQLite3数据库！现在尝试创建...\n";
            $this->createDatabase();
        }
    }

    /**
     * 连接到数据库
     */
    private function connect()
    {
        $this->db = new SQLite3(self::SQLITE_3);
    }

    /**
     * 创建数据库文件
     */
    private function createDatabase()
    {
        $this->db = new SQLite3(self::SQLITE_3);
        $this->createTables();
    }

    /**
     * 创建基本表
     */
    private function createTables()
    {
        // 创建 Users 表
        $createUsersTableSql = "CREATE TABLE Users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            password TEXT NOT NULL,
            email TEXT NOT NULL UNIQUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );";

        $this->db->exec($createUsersTableSql);

        // 创建 Admin 表
        $createAdminTableSql = "CREATE TABLE Admin (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            admin_name TEXT NOT NULL UNIQUE,
            admin_password TEXT NOT NULL,
            admin_email TEXT NOT NULL UNIQUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );";

        $this->db->exec($createAdminTableSql);

        echo "基本表 Users 和 Admin 创建成功。\n";
    }

    /**
     * 检查并创建基本表（如果不存在）
     */
    private function createTablesIfNotExists()
    {
        // 检查 Users 表是否存在
        $checkUsersTableSql = "SELECT name FROM sqlite_master WHERE type='table' AND name='Users';";
        $result = $this->db->query($checkUsersTableSql);
        if (!$result->fetchArray()) {
            $createUsersTableSql = "CREATE TABLE Users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT NOT NULL UNIQUE,
                password TEXT NOT NULL,
                email TEXT NOT NULL UNIQUE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );";
            $this->db->exec($createUsersTableSql);
        }

        // 检查 Admin 表是否存在
        $checkAdminTableSql = "SELECT name FROM sqlite_master WHERE type='table' AND name='Admin';";
        $result = $this->db->query($checkAdminTableSql);
        if (!$result->fetchArray()) {
            $createAdminTableSql = "CREATE TABLE Admin (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                admin_name TEXT NOT NULL UNIQUE,
                admin_password TEXT NOT NULL,
                admin_email TEXT NOT NULL UNIQUE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );";
            $this->db->exec($createAdminTableSql);
        }
    }

    /**
     * 插入数据
     * @param string $table 表名
     * @param array $data 数据数组
     * @return bool 插入是否成功
     */
    public function insert(string $table, array $data): bool
    {
        $columns = implode(", ", array_keys($data));
        $values = ":" . implode(", :", array_keys($data));

        $sql = "INSERT INTO $table ($columns) VALUES ($values);";
        $stmt = $this->db->prepare($sql);

        foreach ($data as $key => $value) {
            $stmt->bindValue(":$key", $value);
        }

        return $stmt->execute() !== false;
    }

    /**
     * 删除数据
     * @param string $table 表名
     * @param array $conditions 条件数组
     * @return bool 删除是否成功
     */
    public function delete(string $table, array $conditions): bool
    {
        $where = [];
        foreach ($conditions as $key => $value) {
            $where[] = "$key = :$key";
        }
        $where = implode(" AND ", $where);

        $sql = "DELETE FROM $table WHERE $where;";
        $stmt = $this->db->prepare($sql);

        foreach ($conditions as $key => $value) {
            $stmt->bindValue(":$key", $value);
        }

        return $stmt->execute() !== false;
    }

    /**
     * 查询数据
     * @param string $table 表名
     * @param array $conditions 条件数组
     * @return array 查询结果
     */
    public function select(string $table, array $conditions = []): array
    {
        $where = [];
        if (!empty($conditions)) {
            foreach ($conditions as $key => $value) {
                $where[] = "$key = :$key";
            }
            $where = "WHERE " . implode(" AND ", $where);
        }

        $sql = "SELECT * FROM $table $where;";
        $stmt = $this->db->prepare($sql);

        if (!empty($conditions)) {
            foreach ($conditions as $key => $value) {
                $stmt->bindValue(":$key", $value);
            }
        }

        $result = $stmt->execute();
        return $result ? $result->fetchArray(SQLITE3_ASSOC) : [];
    }

    /**
     * 执行任意SQL语句
     * @param string $sql SQL语句
     * @return mixed 执行结果
     */
    public function customControl(string $sql)
    {
        return $this->db->exec($sql) !== false;
    }

    /**
     * 关闭数据库连接
     */
    public function close()
    {
        $this->db->close();
    }
}